package tg1;

 import java.sql.*;
 import javax.sql.*;
 import java.util.*;
 import java.util.HashMap;
 import javax.naming.*;
import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
 
public class JdbcDAO {

	private static JdbcDAO jdbcDAO =null;
	private static Connection conn;
	//private static final String connectionUrl= "jdbc:postgresql://localhost:5432/tg1" ;
	private static final String connectionUrl= "jdbc:postgresql://localhost:5432/testetracker2" ;
	
	/*connection dos batches*/
	private static Connection batchesConn=null;
	/*batches para refresh do tracker*/
	private static PreparedStatement insertBatch=null;
	private static PreparedStatement historyBatch=null;
	private static PreparedStatement updateBatch=null;
	private static PreparedStatement stoppedBatch=null;
	private static PreparedStatement completedBatch=null;
	private static PreparedStatement registeredPeersBatch=null;
	private static PreparedStatement unregisteredPeersBatch=null;
	
	/*tempo ate proximo anuncio: 35 min*/
	//public static final String ANNOUNCE_INTERVAL = "2100";
	public static final String ANNOUNCE_INTERVAL = "10";
	/*tempo minimo at proximo anuncio: 3 min*/
	public static final String MIN_ANNOUNCE_INTERVAL = "180";
	
	private static final int RESPONSE_MAXPEERS = 30;
	
	public static final boolean SEEDER = true;
	public static final boolean LEECHER = false;
	public static final String DOWNLOADED =  "downloaded";
	public static final String UPLOADED =  "uploaded";

	private static int numAddedPeers=0;
	private static int numStoppedPeers=0;
	
//testes com pooled connections
  //private  static DataSource datasource;
  private  static  Jdbc3PoolingDataSource datasource;
  //private  static Context envCtx;	
  //private  static Hashtable env; 
	
	 /* o construtor privado e o getBEncoder implementam o padrao singleton*/	
 
	//sem pooling
	 /*private JdbcDAO(){
		
		try{	//registrando o driver do postgres no drivermanager
			Class.forName("org.postgresql.Driver");
			System.out.println("Driver do Postgres registrado com sucesso.");
		}catch (Exception e){
			System.out.println ("Erro registrando o driver do postgres no drivermanager:");
			e.printStackTrace();
		}		
	}*/
  //com pooling
  /*private JdbcDAO(){
		
		try{	//registrando o driver do postgres no drivermanager
			//Class.forName("org.postgresql.Driver");
			 env = new Hashtable();
			env.put(Context.INITIAL_CONTEXT_FACTORY, 
			"com.sun.jndi.fscontext.RefFSContextFactory");
			envCtx = new InitialContext(env);
			//envCtx = (Context) new InitialContext().lookup("java:comp/env");
			//datasource = (DataSource) envCtx.lookup("jdbc/MyDataSource");
			datasource = (DataSource) envCtx.lookup("jdbc:postgresql://localhost:5432/testetracker2");
			System.out.println("Driver do Postgres registrado com sucesso.");
		}catch (Exception e){
			System.out.println ("Erro registrando o driver do postgres no drivermanager:");
			e.printStackTrace();
		}		
	}
 */
	private JdbcDAO(){
		
		try{
			datasource = new Jdbc3PoolingDataSource();
			datasource.setDataSourceName("Uma fonte de dados do tg");
			datasource.setServerName("localhost:5432");
			datasource.setDatabaseName("testetracker2");
			datasource.setUser("andre");
			datasource.setPassword("andre");
			datasource.setMaxConnections(5);
/*//tentativa de usar JNDI
			envCtx =new InitialContext();
			System.out.println("jaspion.");
			envCtx.bind("DataSource", source);
			System.out.println("blerbs.");
			datasource= (DataSource)new InitialContext().lookup("DataSource");
*/
			
			System.out.println("Driver do Postgres registrado com sucesso.");
		}catch (Exception e){
			System.out.println ("Erro registrando o driver do postgres no drivermanager:");
			e.printStackTrace();
		}		
	}

	/*se nenhuma instancia de JdbcDAO foi criada ainda, cria uma e retorna
 	senao, retorna a instancia que jah foi criada*/
	//sem pooling
		public synchronized static JdbcDAO getDAO() {
	         if( jdbcDAO == null ) {
	             jdbcDAO = new JdbcDAO();
        	 }
        	 return jdbcDAO;
	}
	
	
	
	/****************************************************************************/
	/****************************************************************************/
	// com pooling
	public static Connection getDBConnection(){
		
		try {	
			if (jdbcDAO ==null){
				System.out.println ("jdbcDAO eh nulo. inicializando essa merda:");
				getDAO();
			}
			System.out.println ("jdbcDAO nao eh nulo. catando connection:");
			//sem pooling
			//conn = DriverManager.getConnection(connectionUrl, "andre", "andre");	
			//sem pooling
			Connection conn = datasource.getConnection();
			System.out.println ("conn.getTransactionIsolation "+conn.getTransactionIsolation());
			//conn.setAutoCommit(false);
			//System.out.println ("conn.toString()"+conn.toString());
			return conn;
						
		}catch (Exception e){
			System.out.println ("Erro obtendo conexao em getDBConnection:");
			e.printStackTrace();
			return null;
		}
		
	}
	
	public static void closeConnection(Connection conn){
		try {	
			if (conn== null)
				{ System.out.println("Erro em closeConnection: connection para fechar eh nula.");
				}
			
				
			if (conn.isClosed())
				{System.out.println("Erro em closeConnection: connection para fechar jah esta fechada.");
				throw new RuntimeException();
				}
			else {
				//conn.commit();
				conn.close();}
			
		}catch (Exception e){
			System.out.println ("Exception em closeConnection:");
			e.printStackTrace();
			try{
				if (!conn.isClosed()){
					System.out.println("fechando  connection aberta .");
					//conn.rollback();
					conn.close();
				}	
			}catch (Exception e2){
					System.out.println ("Exception em closeConnection dentro do catch da exception:");
					e2.printStackTrace();
			}
		}
	}
	/****************************************************************************/
	/****************************************************************************/
		
	public static  ResultSet getPeers(Connection conn, Map peerDataMap) throws Exception{
	
		PreparedStatement pstmt = conn.prepareStatement(getPeersQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		pstmt.setString(1,(String) peerDataMap.get("info_hash"));
		
		/*o tracker deve entregar no maximo RESPONSE_MAXPEERS peers*/
		int maxpeers =Integer.parseInt( (String) peerDataMap.get("numwant"));
		
		if ( maxpeers < RESPONSE_MAXPEERS){
			pstmt.setInt(2, maxpeers );
		}	
		else pstmt.setInt(2,RESPONSE_MAXPEERS);
		
		pstmt.setFetchSize(50);
		System.out.println (pstmt.toString());
		
		ResultSet rs = pstmt.executeQuery();
				
	//	pstmt.close();
		
		return rs;
	}
	
	/****************************************************************************/
	/****************************************************************************/
	public  static  void insertNewPeer(Map peerDataMap){
		Connection conn=null;
		try{	
			 conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(insertNewPeerQuery);
			
			pstmt.setString(1, (String) peerDataMap.get("info_hash"));
			pstmt.setString(2, (String) peerDataMap.get("peer_id"));
			pstmt.setString(3, (String) peerDataMap.get("ip"));
			pstmt.setInt(4, (((Integer) peerDataMap.get("port")).intValue() ) );
			pstmt.setBytes(5, (byte[]) peerDataMap.get("ipembytes"));
			pstmt.setLong(6, System.currentTimeMillis());
					
			pstmt.executeUpdate();
			
			if((TrackerProperties.getTrackerProperties().getProperty("tracker.PUBLIC_TRACKER")).equals("false")){
				pstmt = conn.prepareStatement(addTorrentToHistoryQuery);
				pstmt.setString(1, (String) peerDataMap.get("pid"));
				pstmt.setString(2, (String) peerDataMap.get("info_hash"));
				pstmt.executeUpdate();
			}
			
			numAddedPeers++;
			
			pstmt.close();

		}catch (Exception e){
			System.out.println ("Erro em InsertNewPeer:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	
	/****************************************************************************/
	/****************************************************************************/
	
	public static  void updatePeerDownloadStats(Map peerDataMap) {
		Connection conn=null ;
		try{
			conn = getDBConnection();
			PreparedStatement pstmt ;
			ResultSet rs;
			int downloaded = Integer.parseInt( (String) peerDataMap.get("downloaded"));
			int uploaded=0;
			
			int leftAtual = Integer.parseInt( (String) peerDataMap.get("left"));
			String pid = (String)peerDataMap.get("pid");
			String info_hash = (String)peerDataMap.get("info_hash");
			String  peer_id =(String) peerDataMap.get("peer_id");
			String event=(String)peerDataMap.get("event");
			long lastupdate = System.currentTimeMillis();
			
			/*soh atualiza upload do peer qdo cliente der stop*/
			if ((event!=null) && (event.equals("stopped"))){
				 uploaded = Integer.parseInt( (String) peerDataMap.get("uploaded"));
			}
			
			pstmt = conn.prepareStatement(peerSearchQuery);
			pstmt.setString(1, info_hash);
			pstmt.setString(2, peer_id);
			/*setando o lastupdate*/
			//pstmt.setLong(2, System.currentTimeMillis());
			//System.out.println (":updatePeerDownloadStats: 1o pstmt= "+pstmt.toString());
			rs = pstmt.executeQuery();
			/*se peer nao existir no banco, adiciona ele . senao da um update nos stats dele.*/
			if (!rs.next()){
				insertNewPeer(peerDataMap);
			}
			
			/*atualiza a tabela de peers com os valores mais atuais de downloaded, uploaded e left
			se houver um pid na url de announce entao atualiza a tabela users tambem (com os mesmos valores)*/
			if (pid!= null){
					pstmt = conn.prepareStatement(updateRegisteredPeerQuery);
					pstmt.setLong(4, lastupdate);
					pstmt.setString(5, peer_id);
					pstmt.setString(6, info_hash);
			}
			else {pstmt = conn.prepareStatement(updateUnregisteredPeerQuery);}
			
			pstmt.setString(1, info_hash);
			pstmt.setInt(2, leftAtual);
			pstmt.setInt(3, uploaded);
			
			/*lastupdate*/
			pstmt.setLong(4, lastupdate);
			pstmt.setString(5, peer_id);
			pstmt.setString(6, info_hash);
			
			System.out.println (":updatePeerDownloadStats: 2o pstmt= "+pstmt.toString());
			long tempoInicio=System.currentTimeMillis();
			System.out.println("updatePeerDownloadStats:comecei a query \n tempo em millis: " +tempoInicio);
			pstmt.executeUpdate();
			System.out.println("updatePeerDownloadStats:terminei a query \n tempo em millis: " +( System.currentTimeMillis()-tempoInicio));
			pstmt.close();
			
			
		}catch (Exception e){
			System.out.println ("Erro em :updatePeerDownloadStats");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	
	
	/****************************************************************************/
	/****************************************************************************/
	public  static void markPeerAsStopped( Map peerDataMap)  {
	
		Connection conn=null ;
		try{
			conn = getDBConnection();
			PreparedStatement pstmt ;
			
			pstmt = conn.prepareStatement(markPeerAsStoppedQuery);
			pstmt.setString(1, (String)peerDataMap.get("info_hash"));
			pstmt.setString(2, (String)peerDataMap.get("peer_id"));
			
			pstmt.executeUpdate();
			
			numStoppedPeers++;
			pstmt.close();
		}catch (Exception e){
			System.out.println ("Erro em :markPeerAsStopped");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
        }
	
	/****************************************************************************/
	/****************************************************************************/
	public  static void deleteAllStoppedPeers() throws Exception {
	
		Connection conn=null ;
		try{
			conn = getDBConnection();
			PreparedStatement pstmt ;
			
			pstmt = conn.prepareStatement(deleteAllStoppedPeersQuery);
			pstmt.executeUpdate();
			
			numStoppedPeers=0;
			pstmt.close();
			/*retorna a conexao para o pool*/
			closeConnection(conn);
				
		}catch (Exception e){
			System.out.println ("Erro em :deleteAllStoppedPeers");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
        }					
	/****************************************************************************/
	/****************************************************************************/
	public  static void unmarkStartedPeers() throws Exception {
	
		Connection conn=null ;
		try{
			conn = getDBConnection();
			PreparedStatement pstmt ;
			
			pstmt = conn.prepareStatement(unmarkStartedPeersQuery);
			pstmt.executeUpdate();
			
			numAddedPeers=0;
				
		}catch (Exception e){
			System.out.println ("Erro em :unmarkStartedPeersQuery");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
        }					
	
			
	/****************************************************************************/
	/****************************************************************************/		
	public  static ResultSet getModifiedPeers(Connection conn, long timeout) throws Exception {
	
		try{
			PreparedStatement pstmt ;
			
			//pstmt = conn.prepareStatement(getModifiedPeersQuery);
			if (TrackerProperties.getTrackerProperties().getProperty("tracker.publictracker.LOG_USER_DOWNLOADS").equals("true"))
				{ pstmt = conn.prepareStatement(getModifiedPeersQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);}
			
			else if (TrackerProperties.getTrackerProperties().getProperty("tracker.ratio.RATIO_SYSTEM_ACTIVE").equals("true")
				&& TrackerProperties.getTrackerProperties().getProperty("tracker.ratio.tracker.ratio.RATIO_UPDATE").equals("onannounce"))
				{ pstmt = conn.prepareStatement(getModifiedPeersQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);}	
			
			else { pstmt = conn.prepareStatement(getAddedAndStoppedPeersQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);}	
			
			pstmt.setLong(1, timeout);
			ResultSet rs = pstmt.executeQuery();
			return rs;
				
		}catch (Exception e){
			System.out.println ("Erro em :getModifiedPeers");
			e.printStackTrace();
			return null;
		}		
        }					
	/****************************************************************************/
	/****************************************************************************/
	public  static ResultSet getAmountOfModifiedPeers(Connection conn, long timeout) throws Exception {
	
		try{
			PreparedStatement pstmt ;
			
			//pstmt = conn.prepareStatement(getAmountOfModifiedPeersQuery);
			if (TrackerProperties.getTrackerProperties().getProperty("tracker.publictracker.LOG_USER_DOWNLOADS").equals("true"))
				{ pstmt = conn.prepareStatement(getAmountOfModifiedPeersQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);}
			
			else if (TrackerProperties.getTrackerProperties().getProperty("tracker.ratio.RATIO_SYSTEM_ACTIVE").equals("true")
				&& TrackerProperties.getTrackerProperties().getProperty("tracker.ratio.tracker.ratio.RATIO_UPDATE").equals("onannounce"))
				{ pstmt = conn.prepareStatement(getAmountOfModifiedPeersQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);}	
			
			else { pstmt = conn.prepareStatement(getAmountOfAddedAndStoppedPeersQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);}	
			
			pstmt.setLong(1, timeout);
			System.out.println ("pstmt: " +pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			return rs;
				
		}catch (Exception e){
			System.out.println ("Erro em :getAmountOfModifiedPeers");
			e.printStackTrace();
			return null;
		}
		//finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
		
        }
	/****************************************************************************/
	/****************************************************************************/
	
	public  static int getPeerAmount(Connection conn, Map peerDataMap, boolean completedDownload) throws Exception {
			
		PreparedStatement pstmt;
			
		if (conn == null){
			throw new IllegalStateException("A conexao com o banco eh nula");
		}
			
		if (completedDownload == true){
				pstmt = conn.prepareStatement(getNumSeedersQuery);
		}
		else { pstmt = conn.prepareStatement(getNumLeechersQuery);	}
		
		pstmt.setString(1, (String) peerDataMap.get("info_hash"));
		ResultSet rs = pstmt.executeQuery();
		
		if (!rs.next())
			{ throw new IllegalStateException("ResultSet do getPeerAmount retornando vazio");	}
			
		int total = rs.getInt("total");
		
		rs.close();
		pstmt.close();
		
		return total;
	}
	/****************************************************************************/
	/****************************************************************************/

	
	public  static int getCompletedDownloadsAmount(Connection conn, Map peerDataMap) throws Exception {
			
		PreparedStatement pstmt;
		if (conn == null){
			throw new IllegalStateException("A conexao com o banco eh nula");
		}
			
		pstmt = conn.prepareStatement(getNumCompletedDownloadsQuery);
		
		pstmt.setString(1, (String) peerDataMap.get("info_hash"));
		ResultSet rs = pstmt.executeQuery();
		
		if (!rs.next()){
			//throw new IllegalStateException("ResultSet do getCompletedDownloads retornando vazio");
			return 0;
		}
			
		int total = rs.getInt("completed");
		
		rs.close();
		pstmt.close();
		
		return total;
	}
	
	
	/****************************************************************************/
	/****************************************************************************/
	
		
	public static void insertNewSeeder(Map peerDataMap){
		Connection conn=null;
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(insertNewSeederQuery);
			
			pstmt.setString(1, (String) peerDataMap.get("info_hash"));
			pstmt.setString(2, (String) peerDataMap.get("peer_id"));
			
			pstmt.executeUpdate();
			
			pstmt = conn.prepareStatement(increaseCompletedDownloadsQuery);
			pstmt.setString(1, (String) peerDataMap.get("info_hash"));
			pstmt.executeUpdate();
			
			pstmt.close();
			
		}catch (Exception e){
			System.out.println ("Erro em insertSeeder:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	
	/****************************************************************************/
	/****************************************************************************/
			
	public  static double getUserRatio(Map peerDataMap){
		Connection conn=null;
		double ratio=0;
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(getUserRatioQuery);
			
			pstmt.setString(1, (String) peerDataMap.get("pid"));
			
			ResultSet rs= pstmt.executeQuery();
			
			if (rs.next()){
				ratio=rs.getDouble("ratio");
			}
			pstmt.close();
			
		}catch (Exception e){
			System.out.println ("Erro em getRatio:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
		return ratio;
	}		
			
			
	/****************************************************************************/
	/****************************************************************************/
			
	public  static boolean getTorrentDownloaded(Map peerDataMap){
		Connection conn=null;
		boolean downloaded=false;
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(getTorrentDownloadedQuery);
			
			pstmt.setString(1, (String) peerDataMap.get("pid"));
			pstmt.setString(2, (String) peerDataMap.get("info_hash"));
			
			ResultSet rs= pstmt.executeQuery();
			
			if (rs.next()){
				downloaded=true;
			}
			
			pstmt.close();
			return downloaded;
			
		}catch (Exception e){
			System.out.println ("Erro em getRatio:");
			e.printStackTrace();
			return downloaded;
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}			
			
	/****************************************************************************/
	/****************************************************************************/		
	public  static int getTrafficAmount(Map peerDataMap, String tipoDeTrafego){
		Connection conn=null;
		int amount=0;
		
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(getAmountDownloadedQuery);
			
			pstmt.setString(1, (String) peerDataMap.get("pid"));
			pstmt.setString(2, (String) peerDataMap.get("info_hash"));
			
			ResultSet rs= pstmt.executeQuery();
			
			if (rs.next()){
				amount=rs.getInt(tipoDeTrafego);
			}
			
			pstmt.close();

		}catch (Exception e){
			System.out.println ("Erro em getTrafficAmount:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
		return amount;
	}				
			
	/****************************************************************************/
	/****************************************************************************/		
	
	public  static int getHitAndRuns(Map peerDataMap){
		Connection conn=null;
		int total=0;
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(getHitAndRunsQuery);
			
			pstmt.setString(1, (String) peerDataMap.get("pid"));
			
			ResultSet rs =pstmt.executeQuery();
			
			if (rs.next()){
				total= rs.getInt("total");
			}
			pstmt.close();
			
		}catch (Exception e){
			System.out.println ("Erro em getHitAndRuns:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
		return total;
	}
	/****************************************************************************/
	/****************************************************************************/
	public  static void setTorrentAsHitAndRun(Map peerDataMap, boolean status){
		Connection conn=null;
		boolean downloaded=false;
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(setHitAndRunQuery);
			
			pstmt.setBoolean(1, status);
			pstmt.setString(2, (String) peerDataMap.get("info_hash"));
			pstmt.setString(3, (String) peerDataMap.get("pid"));
			
			pstmt.executeUpdate();
			pstmt.close();
			
		}catch (Exception e){
			System.out.println ("Erro em setTorrentAsHitAndRun:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}				
				
	/****************************************************************************/
	/****************************************************************************/		
	
	public  static void deletePeer(Map peerDataMap){
	
		Connection conn=null;
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(deletePeerQuery);
			
			pstmt.setString(1,  (String) peerDataMap.get("peer_id"));
					
			pstmt.executeUpdate();
			pstmt.close();
				
		}catch (Exception e){
			System.out.println ("Erro em deletePeer:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	/****************************************************************************/
	/****************************************************************************/		
	public static void refreshTracker(String refreshQuery){
		
		Connection conn=null;
		try{	
			conn= getDBConnection();
			PreparedStatement pstmt =conn.prepareStatement(refreshQuery);
			
			pstmt.executeUpdate();
			pstmt.close();
				
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.refreshTracker:");
			e.printStackTrace();
		}
		finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	/****************************************************************************/
	/****************************************************************************/		
	public static void addToInsertBatch(String info_hash, String peer_id, int peerIP, int peerPort, byte[] byteIP, long timestamp){
		try{	
			if (batchesConn==null){
				batchesConn= getDBConnection();
			}
			
			if (insertBatch == null){
				insertBatch = batchesConn.prepareStatement(insertNewPeerQuery);
			}
			String peerIPprovisorio="";
			
			insertBatch.setString(1, info_hash);
			insertBatch.setString(2, peer_id);
			insertBatch.setString(3, peerIPprovisorio);
			insertBatch.setInt(4, peerPort);
			insertBatch.setBytes(5, byteIP);
			insertBatch.setLong(6, timestamp);
			insertBatch.addBatch();
			
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.addToInsertBatch:");
			e.printStackTrace();
		}
		//finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}

	/****************************************************************************/
	/****************************************************************************/		
	public static void addToHistoryBatch(String info_hash, String peer_id){
		
		try{	
			if (batchesConn==null){
				batchesConn= getDBConnection();
			}
			
			if (historyBatch == null){
				historyBatch = conn.prepareStatement(addTorrentToHistoryQuery);
			}
			historyBatch.setString(1, peer_id);
			historyBatch.setString(2, info_hash);
			historyBatch.addBatch();
			
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.addToHistoryBatch:");
			e.printStackTrace();
		}
		//finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	/****************************************************************************/
	/****************************************************************************/		
	public static void addToRegisteredPeersBatch(String info_hash, String peer_id, long downloaded, long  uploaded, long timestamp ){
		
		try{	
			if (batchesConn==null){
				batchesConn= getDBConnection();
			}
	
			if (registeredPeersBatch == null){
				registeredPeersBatch = conn.prepareStatement(refreshRegisteredPeerQuery);
			}
			String peerIPprovisorio="";
			
			registeredPeersBatch.setLong(1, timestamp);
			registeredPeersBatch.setString(2, peer_id);
			registeredPeersBatch.setString(3, info_hash);
			registeredPeersBatch.setLong(4, downloaded);
			registeredPeersBatch.setLong(5, uploaded);
			registeredPeersBatch.setLong(6, timestamp);
			registeredPeersBatch.setString(7, peer_id);
			registeredPeersBatch.setString(8, info_hash);
			registeredPeersBatch.addBatch();
			
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.addToRegisteredPeersBatch:");
			e.printStackTrace();
		}
		//finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	/****************************************************************************/
	/****************************************************************************/		
	public static void addToUnregisteredPeersBatch(String info_hash, String peer_id, long downloaded, long uploaded, long timestamp ){
		try{	
			if (batchesConn==null){
				batchesConn= getDBConnection();
			}
	
			if (registeredPeersBatch == null){
				registeredPeersBatch = conn.prepareStatement(refreshRegisteredPeerQuery);
			}
			String peerIPprovisorio="";
			
			registeredPeersBatch.setLong(1, downloaded);
			registeredPeersBatch.setLong(2, uploaded);
			registeredPeersBatch.setLong(3, timestamp);
			registeredPeersBatch.setString(4, peer_id);
			registeredPeersBatch.setString(5, info_hash);
			registeredPeersBatch.addBatch();
			
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.addToUnregisteredPeersBatch:");
			e.printStackTrace();
		}
		//finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	
	
	/****************************************************************************/
	/****************************************************************************/		
	public static void addToStoppedBatch(String info_hash, String peer_id){
		try{	
			if (batchesConn==null){
				batchesConn= getDBConnection();
			}	
			if (stoppedBatch == null){
				stoppedBatch = conn.prepareStatement(markPeerAsStoppedQuery);
			}
			stoppedBatch.setString(1, info_hash);
			stoppedBatch.setString(2, peer_id);
			stoppedBatch.addBatch();
			
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.addToStoppedBatch:");
			e.printStackTrace();
		}
		//finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}

	/****************************************************************************/
	/****************************************************************************/		
	public static void addToCompletedBatch(String info_hash){
		try{	
			if (batchesConn==null){
				batchesConn= getDBConnection();
			}	
			if (completedBatch == null){
				completedBatch = conn.prepareStatement(increaseCompletedDownloadsQuery);
			}
			completedBatch.setString(1, info_hash);
			completedBatch.addBatch();
			
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.addToCompletedBatch:");
			e.printStackTrace();
		}
		//finally{/*retorna a conexao para o pool*/ closeConnection(conn);	}
	}
	
	/****************************************************************************/
	/****************************************************************************/		
	public static void executeBatches(){
		try{	
			/**TODO o metodo executeBatch dos pstmts retorna um array de int, 
			contendo uma flag indicando se a sql que foi executada naquela posicao 
			completou com sucesso. TRATAR ESSE ARRAY EM BUSCA DE POSSIVEIS ERROS*/
			if (stoppedBatch != null){
				stoppedBatch.executeBatch();
				stoppedBatch.clearBatch();
			}
			
			if (completedBatch != null){
				completedBatch.executeBatch();
				completedBatch.clearBatch();
			}
			
			if (insertBatch != null){
				insertBatch.executeBatch();
				insertBatch.clearBatch();
			}
			
			if (historyBatch != null){
				historyBatch.executeBatch();
				historyBatch.clearBatch();
			}
			
			if (registeredPeersBatch != null){
				registeredPeersBatch.executeBatch();
				registeredPeersBatch.clearBatch();
			}
			
			if (unregisteredPeersBatch != null){
				unregisteredPeersBatch.executeBatch();
				unregisteredPeersBatch.clearBatch();
			}
			
		}catch (Exception e){
			System.out.println ("Erro em JdbcDAO.executeBatches:");
			e.printStackTrace();
		}
		
	}
	
	/****************************************************************************/
	/****************************************************************************/
			
	public static int getTotalNewPeers(){
		return numAddedPeers;
	}
			
	public static int getTotalStoppedPeers(){
		return numStoppedPeers;
	}
	
	/****************************************************************************/
	/****************************************************************************/
			
	/*inicio das queries em sql*/
	/**algumas queries estao repetidas, mas isso eh devido ao fato de que uma mesma query pode resolver problemas diferentes.  */
	public static final String insertNewPeerQuery = "INSERT INTO peers (info_hash, peer_id, downloaded, uploaded, _left, started, stopped, completed, ip, port, ipembytes, lastupdate ) VALUES (?, ?, 0, 0, 0, true, false, false, ?, ?, ?, ? );";
	
	private static final String insertNewSeederQuery = "UPDATE peers SET completed = TRUE WHERE info_hash = ? AND peer_id = ?;";
	
	public static final String addTorrentToHistoryQuery = "INSERT INTO history (fk_user, fk_torrents, downloaded. uploaded, ratio) VALUES (?, ?, 0, 0, 0);";
	
	private static final String getPeersQuery = "SELECT * FROM peers WHERE info_hash = ? AND stopped = false ORDER BY random() LIMIT ?;";
	
	private static final String getNumSeedersQuery = "SELECT count(*) AS total FROM peers WHERE info_hash = ? AND completed = TRUE;";
	
	private static final String getNumLeechersQuery = "SELECT count(*) AS total FROM peers WHERE info_hash = ? AND completed = FALSE;";
	
	private static final String updateUnregisteredPeerQuery = "UPDATE peers SET downloaded =  (select tamanho from torrents where info_hash=?) - ?  , uploaded= uploaded + ?, lastupdate= ? WHERE peer_id = ? AND info_hash = ?;";
	
	private static final String updateRegisteredPeerQuery = "UPDATE history SET downloaded = (select tamanho from torrents where info_hash=?) - ?  , uploaded= uploaded + ?, lastupdate = ? WHERE user_fk = ? AND torrent_fk = ?; UPDATE peers SET lastupdate =? WHERE user_fk = ? AND torrent_fk = ?;";

	public static final String refreshUnregisteredPeerQuery = "UPDATE peers SET downloaded = ? , uploaded= uploaded + ?, lastupdate= ? WHERE peer_id = ? AND info_hash = ?;";
	
	public static final String refreshRegisteredPeerQuery = "UPDATE peers SET lastupdate =? WHERE user_fk = ? AND torrent_fk = ?; UPDATE history SET downloaded = ?  , uploaded= uploaded + ?, lastupdate = ? WHERE user_fk = ? AND torrent_fk = ?; ";
	
	public static final String increaseCompletedDownloadsQuery = "UPDATE torrents SET completed = completed +1 WHERE infohash = ?;";
	
	private static final String getNumCompletedDownloadsQuery = "SELECT completed FROM torrents WHERE info_hash = ?; ";
	
	private static final String getUserRatioQuery = "SELECT avg(downloaded) as ratio FROM history WHERE user_fk = ?; ";
	
	private static final String getHitAndRunsQuery = "SELECT count(*) AS total FROM history WHERE user_fk = ? AND hitandrun = TRUE;";
	
	private static final String getTorrentDownloadedQuery = "SELECT * FROM history WHERE user_fk = ? AND torrent_fk = ?; ";

	private static final String getAmountDownloadedQuery = "SELECT * FROM history WHERE user_fk = ? AND torrent_fk = ?; ";
	
	private static final String setHitAndRunQuery = "UPDATE history SET hitandrun = ? WHERE infohash = ? AND user_fk=?;";

	private static final String getLeftQuery ="SELECT _left FROM peers WHERE info_hash = ? AND peer_id = ?;";	
	
	private static final String peerSearchQuery= "SELECT _left FROM peers WHERE info_hash = ? AND peer_id = ? LIMIT 1;";
	
	private static final String unmarkStartedPeersQuery = "UPDATE peers SET started = false;";
	
	private static final String deletePeerQuery = "DELETE FROM peers WHERE peer_id = ?;";
	
	private static final String deleteAllStoppedPeersQuery = "DELETE FROM peers WHERE stopped = true; ";
	
	public static final String markPeerAsStoppedQuery = "UPDATE peers SET stopped = true WHERE info_hash = ? AND peer_id=?;";
	
	//arrumar a checagem de tempo abaixo
	private static final String getAddedAndStoppedPeersQuery = "SELECT info_hash, peer_id, lastupdate, ipembytes, port, started, stopped, completed, uploaded, downloaded FROM peers WHERE lastupdate < ? AND (completed=true OR started = true) ORDER BY info_hash, stopped;";
	
	private static final String getAmountOfAddedAndStoppedPeersQuery = "SELECT info_hash, count (*) FROM peers WHERE lastupdate < ? AND (completed=true OR started = true)  GROUP BY info_hash ORDER BY info_hash;";
	
	private static final String getModifiedPeersQuery = "SELECT info_hash, peer_id, lastupdate, ipembytes, port, started, stopped, completed, uploaded, downloaded FROM peers WHERE lastupdate < ? ORDER BY info_hash, stopped;";
	
	private static final String getAmountOfModifiedPeersQuery = "SELECT info_hash, count (*) FROM peers WHERE lastupdate <  ?  GROUP BY info_hash ORDER BY info_hash;";

}
